/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database.operate;

import Globals.AllGlobal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.DiCeng_Model;
import model.GongZuoMian_Model;
import model.ZuanChang_Model;
import model.ZuanKong_Model;

/**
 *
 * @author Administrator
 */
public class OperateMyDatabaseImpl {
    
    public static ArrayList getAllResult(int sqlType)throws Exception{
        Class.forName(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("ORG.SQLITE.JDBC"));
        Connection conn =DriverManager.getConnection(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("JDBC:SQLITE:/D:/LABDB.DB"));
        Statement stat = conn.createStatement();
        ArrayList arrayList=new ArrayList();
        String sqlTypeString=java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("");
        if(sqlType== AllGlobal.GONGZUOMIAN_SQL)
            sqlTypeString=java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("GONGZUOMIAN");
        else if(sqlType==AllGlobal.ZUANCHANG_SQL)
            sqlTypeString=java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("ZUANCHANG");
        else if(sqlType==AllGlobal.ZUANKONG_SQL)
            sqlTypeString=java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("ZUANKONG");
        ResultSet rs = stat.executeQuery(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("SELECT * FROM ")+sqlTypeString+java.util.ResourceBundle.getBundle("myproj/globalizefile").getString(";"));
        while(rs.next()){
            if(sqlType==AllGlobal.GONGZUOMIAN_SQL){
                GongZuoMian_Model gongZuoMian_Model=new GongZuoMian_Model();
                gongZuoMian_Model.setMingcheng(rs.getString(1));
                gongZuoMian_Model.setZhitu(rs.getString(2));
                gongZuoMian_Model.setZhituriqi(String.valueOf(rs.getLong(3)));
                gongZuoMian_Model.setHedui(rs.getString(4));
                gongZuoMian_Model.setBilichiString(rs.getString(5));
                gongZuoMian_Model.setKezhang(rs.getString(6));
                gongZuoMian_Model.setBianhao(rs.getString(7));
                gongZuoMian_Model.setGongchengshi(rs.getString(8));
                gongZuoMian_Model.setCaichang(rs.getString(9));
                gongZuoMian_Model.setGzmcd(rs.getString(10));
                gongZuoMian_Model.setGzmzxcdString(rs.getString(11));
                gongZuoMian_Model.setGzmgszbX(rs.getString(12));
                gongZuoMian_Model.setGzmgszbY(rs.getString(13));
                gongZuoMian_Model.setGzmfwj(rs.getString(14));
                gongZuoMian_Model.setAhcjl(rs.getString(15));
                gongZuoMian_Model.setSgqsgszbx(rs.getString(16));
                gongZuoMian_Model.setSgqsgszby(rs.getString(17));
                arrayList.add(gongZuoMian_Model);
            }else if(sqlType==AllGlobal.ZUANCHANG_SQL){
                ZuanChang_Model zuanChang_Model=new ZuanChang_Model();
                zuanChang_Model.setMingcheng(rs.getString(1));
                zuanChang_Model.setShendu(rs.getString(2));
                zuanChang_Model.setXdzbx(rs.getString(3));
                zuanChang_Model.setXdzby(rs.getString(4));
                zuanChang_Model.setGongzuomian(rs.getString(5));
                zuanChang_Model.setYanzhanxing(rs.getString(6));
                zuanChang_Model.setKongxilvString(rs.getString(7));
                arrayList.add(zuanChang_Model);
            }else if(sqlType==AllGlobal.ZUANKONG_SQL){
                ZuanKong_Model zuanKong_Model=new ZuanKong_Model();
                zuanKong_Model.setMingcheng(rs.getString(1));
                zuanKong_Model.setFangwei(rs.getString(2));
                zuanKong_Model.setQingjiao(rs.getString(3));
                zuanKong_Model.setChengkong(rs.getString(4));
                zuanKong_Model.setZhujiangqian(rs.getString(5));
                zuanKong_Model.setShuiya(rs.getString(6));
                zuanKong_Model.setKongshen(rs.getString(7));
                zuanKong_Model.setVa(rs.getString(8));
                zuanKong_Model.setVb(rs.getString(9));
                zuanKong_Model.setVc(rs.getString(10));
                zuanKong_Model.setNiantu(rs.getString(11));
                zuanKong_Model.setShuini(rs.getString(12));
                zuanKong_Model.setZhongya(rs.getString(13));
                zuanKong_Model.setBeizhu(rs.getString(14));
                zuanKong_Model.setZuanchang(rs.getString(15));
                zuanKong_Model.setChushuidianshuipingtouying(rs.getString(16));
                arrayList.add(zuanKong_Model);
            }
        }
        rs.close();
        stat.close();
        conn.close();
        return arrayList;
    }
    public static ArrayList getAllResult(int sqlType,String sqlClause)throws Exception{
        if(sqlType==AllGlobal.ZUANKONG_SQL){
            ArrayList<ZuanKong_Model> allList=new ArrayList<ZuanKong_Model>(); 
            Class.forName(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("ORG.SQLITE.JDBC"));
            Connection conn =DriverManager.getConnection(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("JDBC:SQLITE:/D:/LABDB.DB"));
            Statement stat = conn.createStatement();
            ResultSet rs=stat.executeQuery(sqlClause);
            while(rs.next()){
                ZuanKong_Model zuanKong_Model=new ZuanKong_Model();
                zuanKong_Model.setMingcheng(rs.getString(1));
                zuanKong_Model.setFangwei(rs.getString(2));
                zuanKong_Model.setQingjiao(rs.getString(3));
                zuanKong_Model.setChengkong(rs.getString(4));
                zuanKong_Model.setZhujiangqian(rs.getString(5));
                zuanKong_Model.setShuiya(rs.getString(6));
                zuanKong_Model.setKongshen(rs.getString(7));
                zuanKong_Model.setVa(rs.getString(8));
                zuanKong_Model.setVb(rs.getString(9));
                zuanKong_Model.setVc(rs.getString(10));
                zuanKong_Model.setNiantu(rs.getString(11));
                zuanKong_Model.setShuini(rs.getString(12));
                zuanKong_Model.setZhongya(rs.getString(13));
                zuanKong_Model.setBeizhu(rs.getString(14));
                zuanKong_Model.setZuanchang(rs.getString(15));
                zuanKong_Model.setChushuidianshuipingtouying(rs.getString(16));
                allList.add(zuanKong_Model);
            }           
            stat.close();
            conn.close();
            return allList;
        }else if(sqlType==AllGlobal.ZUANCHANG_SQL){
            ArrayList<ZuanChang_Model> allList=new ArrayList<ZuanChang_Model>();
            Class.forName(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("ORG.SQLITE.JDBC"));
            Connection conn =DriverManager.getConnection(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("JDBC:SQLITE:/D:/LABDB.DB"));
            Statement stat = conn.createStatement();
            ResultSet rs=stat.executeQuery(sqlClause);
            while(rs.next()){
                ZuanChang_Model zuanChang_Model=new ZuanChang_Model();
                zuanChang_Model.setMingcheng(rs.getString(1));
                zuanChang_Model.setShendu(rs.getString(2));
                zuanChang_Model.setXdzbx(rs.getString(3));
                zuanChang_Model.setXdzby(rs.getString(4));
                zuanChang_Model.setGongzuomian(rs.getString(5));
                zuanChang_Model.setYanzhanxing(rs.getString(6));
                zuanChang_Model.setKongxilvString(rs.getString(7));
                allList.add(zuanChang_Model);
            }            
            stat.close();
            conn.close();
            return allList;
        }else if(sqlType==AllGlobal.GONGZUOMIAN_SQL){
            ArrayList<GongZuoMian_Model> allList=new ArrayList<GongZuoMian_Model>();  
            Class.forName(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("ORG.SQLITE.JDBC"));
            Connection conn =DriverManager.getConnection(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("JDBC:SQLITE:/D:/LABDB.DB"));
            Statement stat = conn.createStatement();
            ResultSet rs=stat.executeQuery(sqlClause);
            while(rs.next()){
                GongZuoMian_Model gongZuoMian_Model=new GongZuoMian_Model();
                gongZuoMian_Model.setMingcheng(rs.getString(1));
                gongZuoMian_Model.setZhitu(rs.getString(2));
                gongZuoMian_Model.setZhituriqi(String.valueOf(rs.getLong(3)));
                gongZuoMian_Model.setHedui(rs.getString(4));
                gongZuoMian_Model.setBilichiString(rs.getString(5));
                gongZuoMian_Model.setKezhang(rs.getString(6));
                gongZuoMian_Model.setBianhao(rs.getString(7));
                gongZuoMian_Model.setGongchengshi(rs.getString(8));
                gongZuoMian_Model.setCaichang(rs.getString(9));
                gongZuoMian_Model.setGzmcd(rs.getString(10));
                gongZuoMian_Model.setGzmzxcdString(rs.getString(11));
                gongZuoMian_Model.setGzmgszbX(rs.getString(12));
                gongZuoMian_Model.setGzmgszbY(rs.getString(13));
                gongZuoMian_Model.setGzmfwj(rs.getString(14));
                gongZuoMian_Model.setAhcjl(rs.getString(15));
                gongZuoMian_Model.setSgqsgszbx(rs.getString(16));
                gongZuoMian_Model.setSgqsgszby(rs.getString(17));
                allList.add(gongZuoMian_Model);
            }           
            stat.close();
            conn.close();
            return allList;
        }
        return null;
    }
    public static ArrayList getOne(int sqlType,String sqlClause)throws Exception{
        if(sqlType==AllGlobal.ZUANKONG_SQL){
            ArrayList<ZuanKong_Model> oneList=new ArrayList<ZuanKong_Model>();
            ZuanKong_Model zuanKong_Model=new ZuanKong_Model();
            Class.forName(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("ORG.SQLITE.JDBC"));
            Connection conn =DriverManager.getConnection(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("JDBC:SQLITE:/D:/LABDB.DB"));
            Statement stat = conn.createStatement();
            ResultSet rs=stat.executeQuery(sqlClause);
            while(rs.next()){
                zuanKong_Model.setMingcheng(rs.getString(1));
                zuanKong_Model.setFangwei(rs.getString(2));
                zuanKong_Model.setQingjiao(rs.getString(3));
                zuanKong_Model.setChengkong(rs.getString(4));
                zuanKong_Model.setZhujiangqian(rs.getString(5));
                zuanKong_Model.setShuiya(rs.getString(6));
                zuanKong_Model.setKongshen(rs.getString(7));
                zuanKong_Model.setVa(rs.getString(8));
                zuanKong_Model.setVb(rs.getString(9));
                zuanKong_Model.setVc(rs.getString(10));
                zuanKong_Model.setNiantu(rs.getString(11));
                zuanKong_Model.setShuini(rs.getString(12));
                zuanKong_Model.setZhongya(rs.getString(13));
                zuanKong_Model.setBeizhu(rs.getString(14));
                zuanKong_Model.setZuanchang(rs.getString(15));
                zuanKong_Model.setChushuidianshuipingtouying(rs.getString(16));
                oneList.add(zuanKong_Model);
            }
            
            stat.close();
            conn.close();
            return oneList;
        }else if(sqlType==AllGlobal.ZUANCHANG_SQL){
            ArrayList<ZuanChang_Model> oneList=new ArrayList<ZuanChang_Model>();
            ZuanChang_Model zuanChang_Model=new ZuanChang_Model();
            Class.forName(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("ORG.SQLITE.JDBC"));
            Connection conn =DriverManager.getConnection(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("JDBC:SQLITE:/D:/LABDB.DB"));
            Statement stat = conn.createStatement();
            ResultSet rs=stat.executeQuery(sqlClause);
            while(rs.next()){
                zuanChang_Model.setMingcheng(rs.getString(1));
                zuanChang_Model.setShendu(rs.getString(2));
                zuanChang_Model.setXdzbx(rs.getString(3));
                zuanChang_Model.setXdzby(rs.getString(4));
                zuanChang_Model.setGongzuomian(rs.getString(5));
                zuanChang_Model.setYanzhanxing(rs.getString(6));
                zuanChang_Model.setKongxilvString(rs.getString(7));
                oneList.add(zuanChang_Model);
            }
            
            stat.close();
            conn.close();
            return oneList;
        }else if(sqlType==AllGlobal.GONGZUOMIAN_SQL){
            ArrayList<GongZuoMian_Model> oneList=new ArrayList<GongZuoMian_Model>();
            GongZuoMian_Model gongZuoMian_Model=new GongZuoMian_Model();
            Class.forName(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("ORG.SQLITE.JDBC"));
            Connection conn =DriverManager.getConnection(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("JDBC:SQLITE:/D:/LABDB.DB"));
            Statement stat = conn.createStatement();
            ResultSet rs=stat.executeQuery(sqlClause);
            while(rs.next()){
                gongZuoMian_Model.setMingcheng(rs.getString(1));
                gongZuoMian_Model.setZhitu(rs.getString(2));
                gongZuoMian_Model.setZhituriqi(String.valueOf(rs.getLong(3)));
                gongZuoMian_Model.setHedui(rs.getString(4));
                gongZuoMian_Model.setBilichiString(rs.getString(5));
                gongZuoMian_Model.setKezhang(rs.getString(6));
                gongZuoMian_Model.setBianhao(rs.getString(7));
                gongZuoMian_Model.setGongchengshi(rs.getString(8));
                gongZuoMian_Model.setCaichang(rs.getString(9));
                gongZuoMian_Model.setGzmcd(rs.getString(10));
                gongZuoMian_Model.setGzmzxcdString(rs.getString(11));
                gongZuoMian_Model.setGzmgszbX(rs.getString(12));
                gongZuoMian_Model.setGzmgszbY(rs.getString(13));
                gongZuoMian_Model.setGzmfwj(rs.getString(14));
                gongZuoMian_Model.setAhcjl(rs.getString(15));
                gongZuoMian_Model.setSgqsgszbx(rs.getString(16));
                gongZuoMian_Model.setSgqsgszby(rs.getString(17));
                oneList.add(gongZuoMian_Model);
            }
            
            stat.close();
            conn.close();
            return oneList;
        }
        return null;
    }
    public static ArrayList doGetDiCengAll(){
        ArrayList<DiCeng_Model> list=new ArrayList<DiCeng_Model>();
        try {
            Class.forName("org.sqlite.JDBC");
            Connection conn =DriverManager.getConnection("jdbc:sqlite:/d:/labdb.db");
            Statement stat = conn.createStatement();
            ResultSet rs=stat.executeQuery("select * from diceng;");
            int i=0;
            while(rs.next()){
                DiCeng_Model dcm=new DiCeng_Model();
                dcm.setMc(rs.getString(1));
                dcm.setJl(rs.getString(2));
                dcm.setHd(rs.getString(3));
                dcm.setId(String.valueOf(i++));
                list.add(dcm);
            }   
            stat.close();
            conn.close();
            return list;
        } catch (Exception ex) {
            Logger.getLogger(OperateMyDatabaseImpl.class.getName()).log(Level.SEVERE, null, ex);
            return list;
        }
    }
    public static DiCeng_Model getDiCengOne(String sqlClause){
        DiCeng_Model dcm=new DiCeng_Model();
        try {
            Class.forName("org.sqlite.JDBC");
            Connection conn =DriverManager.getConnection("jdbc:sqlite:/d:/labdb.db");
            Statement stat = conn.createStatement();
            ResultSet rs=stat.executeQuery(sqlClause);
            int i=0;
            while(rs.next()){
                dcm.setMc(rs.getString(1));
                dcm.setJl(rs.getString(2));
                dcm.setHd(rs.getString(3));
                dcm.setId(String.valueOf(i++));
            }   
            stat.close();
            conn.close();
            return dcm;
        } catch (Exception ex) {
            Logger.getLogger(OperateMyDatabaseImpl.class.getName()).log(Level.SEVERE, null, ex);
            return dcm;
        }
    }
    public static boolean doDelete(String sqlClause)throws Exception{
        boolean flag=false;
        Class.forName(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("ORG.SQLITE.JDBC"));
        Connection conn =DriverManager.getConnection(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("JDBC:SQLITE:/D:/LABDB.DB"));
        Statement stat = conn.createStatement();
        flag=stat.execute(sqlClause);
        stat.close();
        conn.close();
        return flag;
    }
    public static boolean doUpdate(String sqlClause)throws Exception{
        boolean flag=false;
        Class.forName(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("ORG.SQLITE.JDBC"));
        Connection conn =DriverManager.getConnection(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("JDBC:SQLITE:/D:/LABDB.DB"));
        Statement stat = conn.createStatement();
        stat.executeUpdate(sqlClause);//UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
        stat.close();
        conn.close();
        return flag;
    }
    public static boolean doAdd(String sqlClause)throws Exception{
        boolean flag=false;
        Class.forName(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("ORG.SQLITE.JDBC"));
        Connection conn =DriverManager.getConnection(java.util.ResourceBundle.getBundle("myproj/globalizefile").getString("JDBC:SQLITE:/D:/LABDB.DB"));
        Statement stat = conn.createStatement();
        flag=stat.execute(sqlClause);
        stat.close();
        conn.close();
        return flag;
    }

}
